SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('dbo.[TrackLog]', N'U') IS NOT NULL
DROP TABLE [dbo].[TrackLog]
GO

/****** Object:  Table [dbo].[TrackLog]    Script Date: 5/7/2014 9:01:57 PM ******/
CREATE TABLE [dbo].[TrackLog](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](256) NOT NULL,
	[Code] [nvarchar](64) NULL,
	[DetectedOn] [datetime] NOT NULL,
	[Description] [nvarchar](2048) NULL,
	[ImageData] [varchar](max) NULL,
	[RevisionNumber] [int] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_TrackLog] PRIMARY KEY CLUSTERED 
	([Id] ASC)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
GO


IF OBJECT_ID('dbo.[TrackLog_Log]', N'U') IS NOT NULL
DROP TABLE [dbo].[TrackLog_Log]
GO

/****** Object:  Table [dbo].[TrackLog_Log]    Script Date: 5/7/2014 9:01:57 PM ******/

CREATE TABLE [dbo].[TrackLog_Log](
	[Sr] [bigint] IDENTITY(1,1) NOT NULL,
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](256) NOT NULL,
	[Code] [nvarchar](64) NULL,
	[DetectedOn] [datetime] NOT NULL,
	[Description] [nvarchar](2048) NULL,
	[ImageData] [varchar](max) NULL,
	[RevisionNumber] [int] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_TrackLog_Log] PRIMARY KEY CLUSTERED 
	([Sr] ASC)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO


IF OBJECT_ID('dbo.[TrackLogView]', 'V') IS NOT NULL
DROP VIEW [dbo].[TrackLogView]
GO

/****** Object:  View [dbo].[TrackLogView]    Script Date: 5/7/2014 9:01:57 PM ******/
CREATE VIEW [dbo].[TrackLogView]
AS
	SELECT
	TL.[Id]
	,CO.Id AS [MemberId]
	,CO.TagId AS [TagId]
	,TL.[Name]
	,TL.[Code]
	,TL.[DetectedOn]
	,TL.[Description]
	,TL.[ImageData]
	,TL.[RevisionNumber]
	,TL.[CreatedBy]
	,CM.[Name] AS CreatedByName
	,TL.[CreatedOn]
	,TL.[ModifiedBy]
	,MM.[Name] AS ModifiedByName
	,TL.[ModifiedOn]
	From TrackLog TL
	LEFT JOIN Member CM ON TL.[CreatedBy] = CM.[Id]
	LEFT JOIN Member MM ON TL.[ModifiedBy] = MM.[Id]
	LEFT JOIN Member CO ON TL.[Code] = CO.[Code]

GO
SET ANSI_PADDING OFF 
GO


IF OBJECT_ID('dbo.[usp_TrackLogInsert]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_TrackLogInsert]
GO

/****** Object:  StoredProcedure [dbo].[usp_TrackLogInsert]    Script Date: 5/7/2014 9:01:57 PM ******/
CREATE PROC [dbo].[usp_TrackLogInsert]
	@Id  [uniqueidentifier]
	,@Name [nvarchar](256)
	,@Code [nvarchar](64)  = NULL
	,@DetectedOn [datetime] 
	,@Description [nvarchar](2048)
	,@ImageData [varchar](max)
	,@UserID  [uniqueidentifier]

AS
	SET NOCOUNT ON 
	SET XACT_ABORT ON

	BEGIN TRAN

	INSERT INTO [dbo].[TrackLog] ([Id], [Name], [Code], [DetectedOn], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT @Id, @Name, @Code, @DetectedOn, @Description, @ImageData, 0, @UserId, GETDATE(), NULL, NULL

	INSERT INTO [dbo].[TrackLog_log] ([Id], [Name], [Code], [DetectedOn], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT
	TL.[Id]
	,TL.[Name]
	,TL.[Code]
	,TL.[DetectedOn]
	,TL.[Description]
	,TL.[ImageData]
	,TL.[RevisionNumber]
	,TL.[CreatedBy]
	,TL.[CreatedOn]
	,TL.[ModifiedBy]
	,TL.[ModifiedOn]
	From TrackLog TL

	WHERE TL.[Id] = @Id


	COMMIT;

	SELECT
	TL.[Id]
	,TL.[Name]
	,TL.[Code]
	,TL.[DetectedOn]
	,TL.[Description]
	,TL.[ImageData]
	,TL.[RevisionNumber]
	,TL.[CreatedBy]
	,TL.[CreatedOn]
	,TL.[ModifiedBy]
	,TL.[ModifiedOn]
	From TrackLog TL
	WHERE TL.[Id] = @Id

GO


IF OBJECT_ID('dbo.[usp_TrackLogUpdate]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_TrackLogUpdate]
GO

/****** Object:  StoredProcedure [dbo].[usp_TrackLogUpdate]    Script Date: 5/7/2014 9:01:57 PM ******/
CREATE PROC [dbo].[usp_TrackLogUpdate]
	@Id  [uniqueidentifier]
	,@Name [nvarchar](256)
	,@Code [nvarchar](64)  = NULL
	,@DetectedOn [datetime] 
	,@Description [nvarchar](2048)
	,@ImageData [varchar](max)
	,@UserID  [uniqueidentifier]

AS
	SET NOCOUNT ON 
	SET XACT_ABORT ON

	BEGIN TRAN

	UPDATE [dbo].[TrackLog]
	SET [Id] = @Id, [Name] = @Name, [Code] = @Code, [DetectedOn] = @DetectedOn, [Description] = @Description, [ImageData] = @ImageData, [RevisionNumber] = [RevisionNumber] + 1, [ModifiedBy] = @UserId, [ModifiedOn] = GETDATE()	WHERE [Id] = @Id


	INSERT INTO [dbo].[TrackLog_log] ([Id], [Name], [Code], [DetectedOn], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT
	TL.[Id]
	,TL.[Name]
	,TL.[Code]
	,TL.[DetectedOn]
	,TL.[Description]
	,TL.[ImageData]
	,TL.[RevisionNumber]
	,TL.[CreatedBy]
	,TL.[CreatedOn]
	,TL.[ModifiedBy]
	,TL.[ModifiedOn]
	From TrackLog TL

	WHERE TL.[Id] = @Id


	COMMIT;

	SELECT
	TL.[Id]
	,TL.[Name]
	,TL.[Code]
	,TL.[DetectedOn]
	,TL.[Description]
	,TL.[ImageData]
	,TL.[RevisionNumber]
	,TL.[CreatedBy]
	,TL.[CreatedOn]
	,TL.[ModifiedBy]
	,TL.[ModifiedOn]
	From TrackLog TL
	WHERE TL.[Id] = @Id

GO


IF OBJECT_ID('dbo.[usp_TrackLogDelete]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_TrackLogDelete]
GO

/****** Object:  StoredProcedure [dbo].[usp_TrackLogDelete]    Script Date: 5/7/2014 9:01:57 PM ******/
CREATE PROC [dbo].[usp_TrackLogDelete]
	@Id uniqueidentifier
AS
SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRAN
	DELETE FROM [dbo].[TrackLog] WHERE  [Id] = @Id
COMMIT;
GO

